﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Petaframe.Generation
{
    public class SQLDataProvider : IDataProvider
    {

        #region IDataProvider Members

        public string ConnectionString { get; set; }
        public string TableName { get; set; }
        public string PrimaryKeyColumnName { get; set; }
        public string EnumColumnName { get; set; }
        public string PrimaryKeyColumnsAsParameter { get; set; }

        public DataTable TabloBilgileriniAl()
        {
            string sqlText = @"WITH PRIMARY_COLUMN_NAME AS
                            (
	                            SELECT 
									KU.COLUMN_NAME
								FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
								INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU 
									ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
								WHERE TC.TABLE_NAME = @table_name
                            ),
                            
                            IDENTITY_COLUMN_NAME AS
                            (
								 SELECT 
									o.name, 
									c.name AS COLUMN_NAME
								FROM sys.objects o 
										INNER JOIN sys.columns c on o.object_id = c.object_id
								WHERE c.is_identity = 1 and o.name = @table_name
                            )
                            --select * from PRIMARY_COLUMN_NAME

                            SELECT
	                            ISC.COLUMN_NAME
	                            , ISC.DATA_TYPE AS DB_TYPE
	                            , ( CASE WHEN ISC.DATA_TYPE = 'bigint' THEN 'long'
			                                WHEN ISC.DATA_TYPE = 'smallint' THEN 'short'
			                                WHEN ISC.DATA_TYPE = 'binary' 
			                                    OR ISC.DATA_TYPE = 'image'
			                                    OR ISC.DATA_TYPE='timestamp'
			                                    OR ISC.DATA_TYPE = 'varbinary' THEN 'byte[]'
			                                WHEN ISC.DATA_TYPE = 'bit' THEN 'bool'
			                                WHEN ISC.DATA_TYPE = 'tinyint' THEN 'byte'
	                                        WHEN ISC.DATA_TYPE = 'char' AND ISC.CHARACTER_MAXIMUM_LENGTH = 1 THEN 'char'
			                                WHEN ISC.DATA_TYPE = 'char' AND ISC.CHARACTER_MAXIMUM_LENGTH > 1 THEN 'string'
			                                WHEN ISC.DATA_TYPE = 'date' 
				                                OR ISC.DATA_TYPE = 'datetime'
				                                OR ISC.DATA_TYPE = 'smalldatetime' THEN 'DateTime'
			                                WHEN ISC.DATA_TYPE = 'datetimeoffset' THEN 'DateTimeOffset'
			                                WHEN ISC.DATA_TYPE = 'time' THEN 'TimeSpan'
			                                WHEN ISC.DATA_TYPE = 'decimal'
			                                    OR ISC.DATA_TYPE = 'money' 
			                                    OR ISC.DATA_TYPE = 'numeric' 
			                                    OR ISC.DATA_TYPE = 'smallmoney' THEN 'decimal'
			                                WHEN ISC.DATA_TYPE = 'int' THEN 'int'
			                                WHEN ISC.DATA_TYPE = 'sql_variant' THEN 'object'
			                                WHEN ISC.DATA_TYPE = 'float' THEN 'double'
			                                WHEN ISC.DATA_TYPE = 'real' THEN 'Single'
			                                WHEN ISC.DATA_TYPE = 'uniqueidentifier' THEN 'Guid'
			                                WHEN ISC.DATA_TYPE = 'geography' 
			                                    OR ISC.DATA_TYPE = 'hierarchyid'
                                                OR ISC.DATA_TYPE = 'geometry'
			                                    OR ISC.DATA_TYPE = 'nchar'
			                                    OR ISC.DATA_TYPE = 'ntext'
			                                    OR ISC.DATA_TYPE = 'nvarchar'
			                                    OR ISC.DATA_TYPE = 'varchar'
			                                    OR ISC.DATA_TYPE = 'xml'
			                                    OR ISC.DATA_TYPE = 'text' THEN 'string'
			 
		                            END) AS CODE_TYPE
	                            , PCN.COLUMN_NAME AS PRIMARY_KEY_COLUMN
	                            , ICN.COLUMN_NAME AS IDENTITY_KEY_COLUMN
                            FROM INFORMATION_SCHEMA.COLUMNS AS ISC
                            LEFT OUTER JOIN PRIMARY_COLUMN_NAME AS PCN ON ISC.COLUMN_NAME = PCN.COLUMN_NAME
                            LEFT OUTER JOIN IDENTITY_COLUMN_NAME AS ICN ON ISC.COLUMN_NAME = ICN.COLUMN_NAME
                            WHERE TABLE_NAME = @table_name
                            ORDER BY ORDINAL_POSITION";

            DataTable dt = new DataTable();
            SqlConnection sqlCon = null;

            try
            {
                sqlCon = new SqlConnection(this.ConnectionString);
                sqlCon.Open();

                SqlCommand sqlCom = new SqlCommand(sqlText, sqlCon);
                SqlParameter param = new SqlParameter();
                param.DbType = DbType.String;
                param.ParameterName = "@table_name";
                param.Value = this.TableName;
                sqlCom.Parameters.Add(param);
                SqlDataAdapter adapter = new SqlDataAdapter(sqlCom);
                adapter.Fill(dt);

                this.PrimaryKeyKolonBelirle(dt);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                sqlCon.Close();
            }

            return dt;
        }

        public DataTable TabloEnumBilgisiniAl()
        {
            string sqlText = @"SELECT * FROM " + this.TableName;

            DataTable dt = new DataTable();
            SqlConnection sqlCon = null;

            try
            {
                sqlCon = new SqlConnection(this.ConnectionString);
                sqlCon.Open();

                SqlCommand sqlCom = new SqlCommand(sqlText, sqlCon);
                SqlDataAdapter adapter = new SqlDataAdapter(sqlCom);
                adapter.Fill(dt);

                foreach (DataColumn column in dt.Columns)
                {
                    if (column.ColumnName != this.PrimaryKeyColumnName)
                    {
                        this.EnumColumnName = column.ColumnName;
                        break;
                    }
                }

            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                sqlCon.Close();
            }

            return dt;
        }

        public void PrimaryKeyKolonBelirle(DataTable dt)
        {
            this.PrimaryKeyColumnName = String.Empty;
            this.PrimaryKeyColumnsAsParameter = String.Empty;

            foreach (DataRow row in dt.Rows)
            {
                if (row["COLUMN_NAME"].ToString() == row["PRIMARY_KEY_COLUMN"].ToString())
                {
                    if (!String.IsNullOrEmpty(this.PrimaryKeyColumnName))
                    {
                        this.PrimaryKeyColumnName += ",";
                        this.PrimaryKeyColumnsAsParameter += ",";
                    }

                    this.PrimaryKeyColumnName += row["COLUMN_NAME"].ToString();
                    this.PrimaryKeyColumnsAsParameter += row["CODE_TYPE"].ToString() + " " + row["COLUMN_NAME"].ToString();
                }
            }
        }
        public DataTable TablolariYukle()
        {
            string sqlText = @" SELECT
	                                TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
                                FROM INFORMATION_SCHEMA.TABLES
                                WHERE TABLE_TYPE = 'BASE TABLE'
                                ORDER BY TABLE_SCHEMA,TABLE_NAME";

            DataTable dt = new DataTable();
            SqlConnection sqlCon = null;

            try
            {
                sqlCon = new SqlConnection(this.ConnectionString);
                sqlCon.Open();

                SqlCommand sqlCom = new SqlCommand(sqlText, sqlCon);
                SqlDataAdapter adapter = new SqlDataAdapter(sqlCom);
                adapter.Fill(dt);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                sqlCon.Close();
            }

            return dt;
        }
        #endregion
    }
}
